﻿using Microsoft.AspNetCore.Mvc;
using PPLess.DataModels;
using PPLess.Helpers;
using reAdmin.Helpers;
using System;
using System.Data;
using System.Data.SqlClient;

namespace PPLess.Controllers
{
    public class SystemController : Controller
    {
        
        #region 文档

        #region  获取文档列表
        public JsonResult getDocumentList(Search search)
        {
            try
            {
                QuerySuite querySuite = new QuerySuite("(select a.*,DocumentIndexName from tblDocument a left join tblDocumentIndex b on a.DocumentIndexID=b.DocumentIndexID  and b.RecordStatus=0 where a.RecordStatus = 0) t", search);
                querySuite.AddParam("Org", "=", SSOClient.Org);
                DataSet ds = SqlHelper.Query(querySuite.LimitSql, querySuite.Params);
               
                return Json(new { code = 0, data = QuerySuite.ToDictionary(ds.Tables[0]), count = QuerySuite.DsCount(ds) });
            }
            catch (Exception e)
            {
                LogHelper.SaveLog(e);
                return Json(new { code = -1 } );
            }
        }
        #endregion

        #region 删除文档数据
        public JsonResult delDocument(string ids)
        {
            try
            {
                QuerySuite querySuite = new QuerySuite("tblDocument");
                querySuite.AddParam("DocumentID", "inid", ids);
                querySuite.AddParam("Org", "=",SSOClient.Org);
                SqlHelper.ExecuteSql( querySuite.DeleteSql, querySuite.Params);
                return Json(new { code = 0 } );

            }
            catch (Exception e)
            {
                LogHelper.SaveLog(e);
                return Json(new { code = -1 } );
            }
        }
        #endregion

        #region 保存文档数据
        //[ValidateInput(false)]
        public JsonResult saveDocument(tblDocument model)
        {
            try
            { 
                QuerySuite querySuite = new QuerySuite("tblDocument where  DocumentID!=@DocumentID and DocumentIndexID=@DocumentIndexID and Title=@Title ");
                querySuite.AddParam("DocumentID", model.DocumentID);
                querySuite.AddParam("DocumentIndexID", model.DocumentIndexID);
                querySuite.AddParam("Title", model.Title);
                querySuite.AddParam("Org", SSOClient.Org);
                if (SqlHelper.Exists(querySuite.countSql, querySuite.Params))
                    return Json(new { code = 1, msg = "文档名称已存在。" });
                if (model.DocumentID == new Guid())
                {
                    model.DocumentID = Guid.NewGuid();
                    model.CreatedOn = DateTime.Now;
                    model.CreatedBy = SSOClient.UserNo;
                    model.RecordStatus = false;
                    model.ImportBatch = 0;
                    model.Org = SSOClient.Org;
                    SqlHelper.InsertSql(model);
                }
                else
                {
                    model.ModifiedBy = SSOClient.UserNo;
                    model.ModifiedOn = DateTime.Now;
                    model.ImportBatch = 0;
                    SqlHelper.UpdateSql(model, "DocumentID", new string[] { "CreatedOn", "CreatedBy", "RecordStatus", "ImportBatch","Org" });
                }


                return Json(new { code = 0,updateData = model });
            }
            catch (Exception e)
            {
                LogHelper.SaveLog(e);
                return Json(new { code = -1 } );
            }
        }
        #endregion

        #region 获取文档数据
        public JsonResult getDocument(Guid? dataId)
        {
            try
            {
                QuerySuite querySuite = new QuerySuite("tblDocument where  DocumentID=@DocumentID  ");
                querySuite.AddParam("DocumentID", dataId);
                querySuite.AddParam("Org","=", SSOClient.Org);
                tblDocument model = ModelHelper<tblDocument>.ConvertToModel(SqlHelper.Query(querySuite.SelectSql, querySuite.Params).Tables[0]);
                return Json(new { code = 0, data = model });

            }
            catch (Exception e)
            {
                LogHelper.SaveLog(e);
                return Json(new { code = -1 });
            }
        }
        public JsonResult getDocuments(Search search, Guid? dataId)
        {
            try
            {
                string userSql = "and ','+DocumentID+',' like '%,'" + dataId + "',%'";

                QuerySuite querySuite = new QuerySuite(string.Format(@"tblDocument where DocumentIndexID='" + dataId + "'"), search);
                querySuite.AddParam("Org", SSOClient.Org);
                DataSet ds = SqlHelper.Query(querySuite.LimitSql, querySuite.Params);
                return Json(new { code = 0, data = QuerySuite.ToDictionary(ds.Tables[0]), count = QuerySuite.DsCount(ds) });
            }
            catch (Exception e)
            {
                LogHelper.SaveLog(e);
                return Json(new { code = -1 } );
            }
        }
        #endregion
        
        #endregion

        #region 文档索引

        #region  获取文档索引列表
        public JsonResult getDocumentIndexList(Search search)
        {
            try
            {
                QuerySuite querySuite = new QuerySuite("tblDocumentIndex where RecordStatus=0", search);
                querySuite.AddParam("Org", "=", SSOClient.Org);
                DataSet ds = SqlHelper.Query(querySuite.LimitSql, querySuite.Params);
                return Json(new { code = 0, data = QuerySuite.ToDictionary(ds.Tables[0]), count = QuerySuite.DsCount(ds) });
            }
            catch (Exception e)
            {
                LogHelper.SaveLog(e);
                return Json(new { code = -1 } );
            }
        }
        #endregion       

        #region 删除文档索引
        public JsonResult delDocumentIndex(string ids)
        {
            try
            {
                //QuerySuite querySuite = new QuerySuite("tblDocumentIndex");
                //querySuite.AddParam("DocumentIndexID", "inid", ids);
                //SqlHelper.ExecuteSql("update tblDocumentIndex set RecordStatus=1 from " + querySuite.FromSql, querySuite.Params);
                //return Json(new { code = 0 } );
                QuerySuite querySuite = new QuerySuite("tblDocumentIndex a join CTE b on a.DocumentIndexID=b.DocumentIndexID");

                string rootWhere = " ',' + @ids + ',' like '%,' + Cast(DocumentIndexID As nvarchar(36)) + ',%'";
                string cte = @"WITH CTE AS 
                                    (
                                    SELECT * FROM tblDocumentIndex WHERE {0}
                                    UNION ALL 
                                    SELECT a.* from tblDocumentIndex a INNER JOIN CTE b ON a.ParentID=b.DocumentIndexID
                                    )";

                querySuite.Cte = string.Format(cte, rootWhere);
                querySuite.AddParam("ids", ids);
                querySuite.AddParam("Org", SSOClient.Org);
                SqlHelper.ExecuteSql(querySuite.Cte + "delete a from " + querySuite.FromSql, querySuite.Params);

                SqlHelper.ExecuteSql(@"
delete t from tblDocument t where not EXISTS (select 1 from tblDocumentIndex b where t.DocumentIndexID=b.DocumentIndexID and t.Org=b.Org)
                     
                    ", new SqlParameter[] {  new SqlParameter("@Org", SSOClient.Org) });
                return Json(new { code = 0 });
            }
            catch (Exception e)
            {
                LogHelper.SaveLog(e);
                return Json(new { code = -1 } );
            }
        }
        #endregion

        #region 保存文档索引
        public JsonResult saveDocumentIndex(tblDocumentIndex model)
        {
            try
            { 
                QuerySuite querySuite = new QuerySuite("tblDocumentIndex where  DocumentIndexID!=@DocumentIndexID and DocumentIndexName=@DocumentIndexName ");
                querySuite.AddParam("DocumentIndexID", model.DocumentIndexID);
                querySuite.AddParam("DocumentIndexName", model.DocumentIndexName);
                querySuite.AddParam("Org","=",SSOClient.Org);
                if (SqlHelper.Exists(querySuite.countSql, querySuite.Params))
                    return Json(new { code = 1, msg = "索引名称已存在。" });
                if (model.DocumentIndexID == new Guid())
                {
                    model.DocumentIndexID = Guid.NewGuid();
                    model.CreatedOn = DateTime.Now;
                    model.CreatedBy = SSOClient.UserNo;
                    model.RecordStatus = false;
                    model.ImportBatch = 0;
                    model.Org = SSOClient.Org;
                    SqlHelper.InsertSql(model);
                }
                else
                {
                    model.ModifiedBy = SSOClient.UserNo;
                    model.ModifiedOn = DateTime.Now;
                    model.ImportBatch = 0;
                    SqlHelper.UpdateSql(model, "DocumentIndexID", new string[] { "CreatedOn", "CreatedBy", "RecordStatus", "ImportBatch","Org" });
                }

                return Json(new { code = 0, updateData = model });
            }
            catch (Exception e)
            {
                LogHelper.SaveLog(e);
                return Json(new { code = -1 } );
            }
        }
        #endregion

        #region 获取文档索引
        public JsonResult getDocumentIndex(Guid? dataId)
        {
            try
            { 
                QuerySuite querySuite = new QuerySuite("tblDocumentIndex where  DocumentIndexID=@DocumentIndexID  ");
                querySuite.AddParam("DocumentIndexID", dataId);
                querySuite.AddParam("Org","=", SSOClient.Org);
                tblDocumentIndex model = ModelHelper<tblDocumentIndex>.ConvertToModel(SqlHelper.Query(querySuite.SelectSql, querySuite.Params).Tables[0]);
                return Json(new { code = 0, data = model } );

            }
            catch (Exception e)
            {
                LogHelper.SaveLog(e);
                return Json(new { code = -1 } );
            }
        }
        #endregion

        #endregion
    }
}
